package helloWorld;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

public class DBInterface {
	private Connection conn;
	int userId = 0;
	 
   /**
    * SingletonHolder is loaded on the first execution of Singleton.getInstance() 
    * or the first access to SingletonHolder.INSTANCE, not before.
    */
   private static class SingletonHolder { 
     private static final DBInterface INSTANCE = new DBInterface();
   }

   public static DBInterface getInstance() {
     return SingletonHolder.INSTANCE;
   }
	public void removeArtistFromDB(String artist)
	{
		String update = "DELETE FROM items " +
						"WHERE artist='"+ artist + "' AND userId='" + userId + "'";	

		try {
			Statement st = conn.createStatement();
			st.executeUpdate(update);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void removeAlbumFromDB(String album)
	{
		String update = "DELETE FROM items " +
						"WHERE album='"+ album + "' AND userId='" + userId + "'";
		
		try {
			Statement st = conn.createStatement();
			st.executeUpdate(update);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<String>  captureArtists(){
		String query = "SELECT DISTINCT artist FROM items WHERE userId='" + userId + "'";
		
		ResultSet set = getSQL(query);
		List<String> artists = new Vector<String>();
		if(set != null)
		{
	      try {
	    	  while (set.next())
	    	  {
	    		  String artist = set.getString("artist");
	    		  artists.add(artist);
			  }
	      } catch (SQLException e) {
			e.printStackTrace();
	      }
		}
		return artists;
	}
	public List<DBLocation> getLocations(){
		String query = 	"SELECT artist, album, price, latitude, longtitude, store " +
						"FROM items " +
						"WHERE userId='" + userId + "'";
		
		ResultSet set = getSQL(query);
		List<DBLocation> result = new Vector<DBLocation>();
		if(set != null)
		{
	      try {
	    	  while (set.next())
	    	  {
	    		  DBLocation loc = new DBLocation();
	    		  loc.artist = set.getString("artist");
	    		  loc.album = set.getString("album");
	    		  loc.price = set.getDouble("price");
	    		  loc.latitude = set.getDouble("latitude");
	    		  loc.longtitude = set.getDouble("longtitude");
	    		  loc.store = set.getString("store");
	    		  result.add(loc);
			  }
	      } catch (SQLException e) {
			e.printStackTrace();
	      }
		}
		return result;
	}
	
	public List<DBLocation> getLocationsOfAlbum(String album){
		String query = 	"SELECT artist, album, price, latitude, longtitude, store " +
						"FROM items " +
						"WHERE album='"+ album + "' AND userId='" + userId + "'";
		
		ResultSet set = getSQL(query);
		List<DBLocation> result = new Vector<DBLocation>();
		if(set != null)
		{
	      try {
	    	  while (set.next())
	    	  {
	    		  DBLocation loc = new DBLocation();
	    		  loc.artist = set.getString("artist");
	    		  loc.album = set.getString("album");
	    		  loc.price = set.getDouble("price");
	    		  loc.latitude = set.getDouble("latitude");
	    		  loc.longtitude = set.getDouble("longtitude");
	    		  loc.store = set.getString("store");
	    		  result.add(loc);
			  }
	      } catch (SQLException e) {
			e.printStackTrace();
	      }
		}
		return result;
	}
	
	public List<String> captureArtistsWithLetter( char c ){
		String query = 	"SELECT DISTINCT artist " +
						"FROM items " +
						"WHERE artist LIKE '" + c + "%' AND userId='" + userId + "'";
		
		ResultSet set = getSQL(query);
		List<String> result = new Vector<String>();
		if(set != null)
		{
	      try {
	    	  while (set.next())
	    	  {
	    		  String artist = set.getString("artist");
	    		  result.add(artist);
			  }
	      } catch (SQLException e) {
			e.printStackTrace();
	      }
		}
		return result;
	}
	
	public List<String> captureAlbumsFromArtist( String artist ){
		String query = 	"SELECT DISTINCT album " +
						"FROM items " +
						"WHERE artist = '"+ artist + "' AND userId='" + userId + "'";
		
		ResultSet set = getSQL(query);
		List<String> result = new Vector<String>();
		if(set != null)
		{
	      try {
	    	  while (set.next())
	    	  {
	    		  String album = set.getString("album");
	    		  result.add(album);
			  }
	      } catch (SQLException e) {
			e.printStackTrace();
	      }
		}
		return result;
	}
	
	private DBInterface(){
		try
		{
			/*
			 * fill in database specifics
			 */
			  Class.forName("com.mysql.jdbc.Driver").newInstance();
			  String url = "jdbc:mysql://localhost/iphone";
			  conn = DriverManager.getConnection(url, "root", "");
		}
		catch (ClassNotFoundException ex) {
			  System.out.println("yups1");
			  System.err.println(ex.getMessage());
			  }
	    catch (IllegalAccessException ex) {
			  System.out.println("yups2");
			  System.err.println(ex.getMessage());
			  }
	    catch (InstantiationException ex) {
			  System.out.println("yups3");
			  System.err.println(ex.getMessage());
			  }
	    catch (SQLException ex)           {
			  System.out.println("yups4");
			  System.err.println(ex.getMessage());
			  }
	    
	}
	private ResultSet getSQL(String query){
		try
	    {
	      Statement st = conn.createStatement();
	      ResultSet rs = st.executeQuery(query);
	      return rs;
	    }
	    catch (SQLException ex)
	    {
	      System.err.println("query error: " + ex.getMessage());
	    }
	    return null;
	}
	
	public void close(){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}
}
